package daoImp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;


import util.DateUtil;

import dao.SearchKeywordDAO;

public class SearchKeywordDAOImp implements SearchKeywordDAO{
	private static SearchKeywordDAOImp uniqueInstance = new SearchKeywordDAOImp();
	private ConnectionPool pool = ConnectionPool.getInstance();
	
	public static SearchKeywordDAOImp getInstance() {
		return uniqueInstance;
	}

	private SearchKeywordDAOImp() {
	}
	
	public ArrayList<String> getKeywordsByTrend(String trend, Date date) {
		String sql = null;
		ArrayList<String> keywords = new ArrayList<String>();
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				e.printStackTrace();
				return keywords;
			}
			Statement stmt = connection.createStatement();
			String dateStr = DateUtil.getNewsDateStr(date);
			trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql = "select keyword from searchkeywords where trend = '" + trend + "' and date = '" + dateStr +"';";
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				String word = rs.getString(1);
				keywords.add(word);
			}
			pool.returnConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return keywords;
	}

	public void saveKeywords(ArrayList<String> keywords, String trend, Date date, boolean isUseful) {
		String sql1 = null;
		String sql2 = null;
		Statement stmt = null;
		Connection connection;
		try {
			connection = pool.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
		try {
			connection.setAutoCommit(false);
			stmt = connection.createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		String dateStr = DateUtil.getNewsDateStr(date);
		trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
		int useful = 0;
		if(isUseful)
			useful = 1;
		sql2 = "delete from searchkeywords where trend = '" + trend + "' and date = '" + dateStr +"' and useful = 0;";
		try {
			stmt.executeUpdate(sql2);
		} catch (SQLException e){
			e.printStackTrace();
		}
		for(String keyword : keywords){
			keyword = keyword.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql1 = "insert into searchkeywords (trend , keyword , date, useful)" + "value( '"
					+ trend + "', '" + keyword + "','"  + dateStr +"'," + useful + ");";
			try {
				stmt.executeUpdate(sql1);
			} catch (SQLException e){
				e.printStackTrace();
			}
		}
		try {
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			pool.returnConnection(connection);
		}
	}

	public ArrayList<String> getUsefulKeywordsByTrend(String trend, Date date) {
		String sql = null;
		ArrayList<String> keywords = new ArrayList<String>();
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				e.printStackTrace();
				return keywords;
			}
			Statement stmt = connection.createStatement();
			String dateStr = DateUtil.getNewsDateStr(date);
			trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql = "select keyword from searchkeywords where trend = '" + trend + "' and date = '" + dateStr +"' and useful = 1;";
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				String word = rs.getString(1);
				keywords.add(word);
			}
			pool.returnConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return keywords;
	}

}
